package com.ruoyi.common.utils.poi;

import com.ruoyi.common.utils.YearUtils;
import com.ruoyi.framework.config.RuoYiConfig;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.*;
@Component
public class CustomExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    static File tmp = new File("");
    static String filePath;

    static {
        try {
            filePath = tmp.getCanonicalPath();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    //  #一览表模板上传文件夹，此文件夹只存放模板文件，若混合其他文件则模板功能失效
    //判断改路径模板时间跟有没有模板
//    linux路径，这里是默认导入模板与判断模板是否存在的路径
//    String str="/root/prod/ruoyi/template";
    //win写法
    String str=filePath+"\\src\\main\\resources\\static\\存放模板";
    private String PATH=str;
    //Set集合保存表格项
    private final Set<String> set=new HashSet<>();

    public CustomExcelUtil() throws IOException {
    }

    //填充Set集合，用于判断表格项和数据的对应
    public void fillSet(Workbook wb) {
        Sheet sheet=wb.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i)!=null){
                for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
                    if (sheet.getRow(i).getCell(j)!=null)
                        set.add(sheet.getRow(i).getCell(j).toString());
                }
            }
        }
    }


    //获取后缀
    public String returnFormat(){
        File file=new File(PATH);
        if (!file.exists())
            file.mkdirs();
        File[] files=file.listFiles();
        if (files==null)
            return "xlsx";
        long tmp=Long.MIN_VALUE;
        String filename = "";
        //比较修改时间
        for (int i = 0; i < Objects.requireNonNull(files).length; i++) {
            long l=files[i].getAbsoluteFile().lastModified();
            if (l>tmp){
                tmp=l;
                filename=files[i].getName();
            }
        }
        if (!filename.equals("")){
            if (filename.endsWith("xls"))
                return "xls";
            else if (filename.endsWith("xlsx"))
                return "xlsx";
        }else
            return "xlsx";
        return "xlsx";
    }

    //读取最近一次上传的模板，没有则使用默认模板
    public Workbook readTemplate() throws IOException {
        File file=new File(PATH);
        if (!file.exists())
            file.mkdirs();
        File[] files=file.listFiles();
        if (files==null)
            return customExcel();

        long tmp=Long.MIN_VALUE;
        int idx=-1;
        //比较修改时间
        for (int i = 0; i < Objects.requireNonNull(files).length; i++) {
            long l=files[i].getAbsoluteFile().lastModified();
            if (l>tmp){
                idx=i;
                tmp=l;
            }
        }
        return idx==-1?customExcel():WorkbookFactory.create(files[idx]);
    }

    Map<String,int[]> mapArea=new HashMap<>();

    //获取合并单元格范围，为填充数据做准备
    public void getArea(Workbook wb) throws IOException {

        Sheet sheet=wb.getSheetAt(0);

        int cnt = sheet.getNumMergedRegions();
        for (int i = 0; i < cnt; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();

            String value=sheet.getRow(firstRow).getCell(firstColumn).toString();

            mapArea.put(value,new int[]{firstRow,lastRow,firstColumn,lastColumn});
        }
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i)!=null){
                for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
                    if (sheet.getRow(i).getCell(j)!=null){
                        if (!mapArea.containsKey(sheet.getRow(i).getCell(j).toString()))
                            mapArea.put(sheet.getRow(i).getCell(j).toString().replaceAll("\r|\n",""),new int[]{i,i,j,j});
                    }
                }
            }
        }
    }
    //填充数据
    public Workbook templateExcel(Map<String,String[]> map) throws IOException {
        //获取模板文件对象
        Workbook wb=readTemplate();
        //填充Set集合
        fillSet(wb);
        //合并单元格范围
        getArea(wb);
        //默认每一个工作簿只有一张表
        Sheet sheet= wb.getSheetAt(0);
        //向模板填充数据
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i)!=null){
                for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
                    if (sheet.getRow(i).getCell(j)!=null){
                        if (set.contains(sheet.getRow(i).getCell(j).toString())&&map.containsKey(sheet.getRow(i).getCell(j).toString())){
                            int row = mapArea.get(sheet.getRow(i).getCell(j).toString())[0];
                            int colum = mapArea.get(sheet.getRow(i).getCell(j).toString())[3]+1;
                            if (map.get(sheet.getRow(i).getCell(j).toString()).length==2){
                                sheet.getRow(row).getCell(colum).setCellValue(map.get(sheet.getRow(i).getCell(j).toString())[0]);
                                int tmp=getLastColum(sheet,row,colum)+1;
                                sheet.getRow(row).getCell(tmp).setCellValue(map.get(sheet.getRow(i).getCell(j).toString())[1]);
                            }
                            else{
                                sheet.getRow(row).getCell(colum).setCellValue(map.get(sheet.getRow(i).getCell(j).toString())[0]);
                            }
                        }
                    }
                }
            }

        }


        for (Map.Entry<String, int[]> entry : mapArea.entrySet()) {
            int[] idx=entry.getValue();
            if (entry.getKey().contains("年度家庭收入一览表"))
                sheet.getRow(idx[0]).getCell(idx[2]).setCellValue(map.get("year")[0] +"年度家庭收入一览表");
            int pre=Integer.parseInt(map.get("year")[0])-1;
            if (entry.getKey().contains("计算收入周期"))
                sheet.getRow(idx[0]).getCell(idx[2]).setCellValue("计算收入周期为："+ pre +"年10月1日——"+map.get("year")[0]+"年9月30日");
        }

        return wb;
    }

    //获取指定合并单元格的尾列
    public int getLastColum(Sheet sheet,int row,int colum){
        int regions = sheet.getNumMergedRegions();
        for (int i = 0; i < regions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow=mergedRegion.getFirstRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            if (firstRow==row){
                if (firstColumn==colum)
                    return lastColumn;
            }
        }
        //返回 colum 表示这不是一个合并单元格(在列上表示)
        return colum;
    }

    //获取其他项前一单元格内容
    //二级标题一定是一个合并单元格，并且一定占据多行。
    //简单来说，三级标题向前找到的第一个占据多行的合并单元格一定是二级标题
    //这里需要判断是不是占据多行，如果只是一个合并单元格且占据了多列而没有占据多行，那么这只是一个三级标题
    public String getTwoTitle(Sheet sheet,int row,int colum){
        int tmp=0;
        int firstR=0;
        int firstC=0;
        int regions = sheet.getNumMergedRegions();
        for (int i = 0; i < regions; i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            int firstRow=mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();

            //先判断这是不是一个三级标题
            if (firstRow!=lastRow){
                //再判断是不是前面的第一个
                if (firstRow<=row&&lastRow>=row){
                    tmp=Math.max(tmp,lastColumn);
                    if (tmp==lastColumn){
                        firstR=firstRow;
                        firstC=firstColumn;
                    }
                    //返回这个单元格的填充内容
                }
            }
        }
        return sheet.getRow(firstR).getCell(firstC).toString();
    }
    //原始模板
    public Workbook customExcel() throws IOException{
        //如果没有模板就默认Linux方式
//        String filePath="/home/ruoyi/uploadPath/一览表模板.xlsx";
        //如果没有模板win方式
        File file = new File("");
        String path = file.getCanonicalPath();
        String filePath=path+"/src/main/resources/static/Template2/一览表模板.xlsx";
        InputStream inputStream = new FileInputStream(filePath);
        //工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        //工作表
   return workbook;

    }

    //文件名编码
    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xls";
        return filename;
    }

    //获取下载地址
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
}
